import pymysql
import json
import requests
from datetime import datetime
from .mysql_config_optimizer import MySQLConfigOptimizer  # 配置优化类
from .mysql_health_checker import MySQLDefectDetector   # 漏洞检测类
from .mysql_anomaly_detector import MySQLAnomalyDetector  # 使用上面定义的异常检测类
import os

# 读取配置文件
def load_config():
    # 获取项目根目录路径
    current_dir = os.path.dirname(__file__)
    project_root = os.path.join(current_dir, '..', '..', '..', '..')
    config_path = os.path.join(project_root, 'UI', 'config.json')
    
    with open(config_path, 'r', encoding='utf-8') as f:
        return json.load(f)

# 全局变量初始化
config = None
current_provider = None
provider_config = None
AI_API_KEY = ''
AI_API_URL = ''
AI_MODEL = ''
AI_TEMPERATURE = 0.7
AI_TOP_P = 0.9
AI_TIMEOUT = 60

# 初始化配置的函数
def init_config():
    global config, current_provider, provider_config, AI_API_KEY, AI_API_URL, AI_MODEL, AI_TEMPERATURE, AI_TOP_P, AI_TIMEOUT
    
    config = load_config()
    current_provider = config['current_provider']
    provider_config = config['providers'][current_provider]

    # 从UI/config.json中获取AI配置
    if provider_config['type'] == 'openai':
        AI_API_KEY = provider_config.get('api_key', '')
        AI_API_URL = provider_config['api_url'] + provider_config['chat_endpoint']
        AI_MODEL = provider_config['model']
    else:
        # 对于ollama等其他类型
        AI_API_KEY = ''  # ollama通常不需要api_key
        AI_API_URL = provider_config['api_url'] + provider_config['chat_endpoint']
        AI_MODEL = provider_config['model']

    # 从UI配置中获取其他参数
    AI_TEMPERATURE = config.get('temperature', 0.7)
    AI_TOP_P = config.get('top_p', 0.9)
    AI_TIMEOUT = config.get('timeout', 60)

REPORT_OUTPUT_FILE = "data/DefectIdentification_data/mysql_data"

class QianwenClient:
    """AI大模型客户端，用于生成优化建议"""
    def __init__(self):
        # 确保配置已初始化
        if config is None:
            init_config()
            
        self.api_key = AI_API_KEY
        self.base_url = AI_API_URL
        self.model = AI_MODEL
        self.temperature = AI_TEMPERATURE
        self.top_p = AI_TOP_P
        self.timeout = AI_TIMEOUT
        
        # 设置请求头
        if provider_config['type'] == 'openai' and self.api_key:
            self.headers = {
                "Content-Type": "application/json",
                "Authorization": f"Bearer {self.api_key}"
            }
        else:
            # 对于ollama等不需要认证的provider
            self.headers = {
                "Content-Type": "application/json"
            }

    def generate_advice(self, analysis_data):
        """调用AI模型生成优化建议"""
        # 自定义JSON编码器，处理特殊类型
        class CustomJSONEncoder(json.JSONEncoder):
            def default(self, obj):
                if isinstance(obj, datetime):
                    return obj.strftime('%Y-%m-%d %H:%M:%S')
                return str(obj)

        # 序列化分析数据
        config_issues = json.dumps(analysis_data['config_issues'], ensure_ascii=False, cls=CustomJSONEncoder)
        vulnerabilities = json.dumps(analysis_data['vulnerabilities'], ensure_ascii=False, cls=CustomJSONEncoder)
        anomalies = json.dumps(analysis_data['anomalies'], ensure_ascii=False, cls=CustomJSONEncoder)
        risk_scan = json.dumps(analysis_data['risk_scan'], ensure_ascii=False, cls=CustomJSONEncoder)

        prompt = f"""请基于以下MySQL数据库的检测数据，生成专业、详细的优化建议报告：

1. 配置问题: {config_issues}
2. 安全漏洞: {vulnerabilities}
3. 性能异常: {anomalies}
4. 风险扫描: {risk_scan}

重要要求：
- 从配置调整、安全加固、性能优化三个维度提供建议
- 要求具体可操作，优先解决高风险问题
- 使用Markdown标题层级（如#、##、###）组织内容
- 重要操作步骤用加粗突出显示
- 代码示例使用```标记
- 不使用表格，全部使用文字描述和列表
- 特别关注连接异常（端口问题、认证失败等）的解决方案"""

        payload = {
            "model": self.model,
            "messages": [{"role": "user", "content": prompt}],
            "temperature": self.temperature,
            "top_p": self.top_p
        }

        try:
            response = requests.post(
                self.base_url,
                headers=self.headers,
                json=payload,
                timeout=self.timeout
            )
            response.raise_for_status()
            return response.json()["choices"][0]["message"]["content"]
        except Exception as e:
            return f"AI模型调用失败: {str(e)}\n请手动分析检测结果"

class MySQLFullAnalyzer:
    """整合所有分析功能的主控制器"""
    def __init__(self, db_config):
        self.db_config = db_config
        self.ai_client = QianwenClient()
        self.analysis_results = {
            "config_issues": [],
            "vulnerabilities": [],
            "anomalies": [],
            "risk_scan": []
        }

    def run_full_analysis(self, scan_servers=None, metrics_duration=60):
        """执行完整分析流程"""
        print("=== 开始MySQL全面分析 ===")

        # 1. 配置优化分析
        print("\n[1/4] 执行配置优化分析...")
        config_optimizer = MySQLConfigOptimizer(self.db_config)
        if config_optimizer.analyze_config_defects():
            self.analysis_results["config_issues"] = config_optimizer.performance_bottlenecks

        # 2. 安全漏洞检测
        print("\n[2/4] 执行安全漏洞检测...")
        defect_detector = MySQLDefectDetector(
            host=self.db_config["host"],
            user=self.db_config["user"],
            password=self.db_config["password"],
            port=self.db_config["port"]
        )
        vuln_result = defect_detector.check_version_vulnerabilities()
        self.analysis_results["vulnerabilities"] = vuln_result.get("vulnerabilities", [])
        defect_detector.close()

        # 3. 性能异常检测（包含连接异常）
        print("\n[3/4] 执行性能异常检测...")
        anomaly_detector = MySQLAnomalyDetector(self.db_config)
        anomaly_detector.collect_metrics(duration_minutes=metrics_duration)
        if anomaly_detector.detect_anomalies():
            self.analysis_results["anomalies"] = anomaly_detector.anomaly_details

        # 4. 远程服务器风险扫描
        if scan_servers:
            print(f"\n[4/4] 执行远程服务器扫描 ({len(scan_servers)}台)...")
            self.analysis_results["risk_scan"] = anomaly_detector.scan_remote_servers(scan_servers)

        print("\n=== 全面分析完成 ===")

    def generate_report(self, output_file):
        """生成Markdown格式的报告"""
        print(f"\n正在调用{provider_config['type'].upper()}模型生成优化建议...")
        ai_advice = self.ai_client.generate_advice(self.analysis_results)

        # 准备各模块的说明文本
        config_desc = f"共发现 {len(self.analysis_results['config_issues'])} 项配置问题" if self.analysis_results["config_issues"] else "基础配置暂未检测到异常"
        vuln_desc = f"共发现 {len(self.analysis_results['vulnerabilities'])} 项已知漏洞" if self.analysis_results["vulnerabilities"] else "未检测到已知CVE漏洞"
        anomaly_desc = f"共检测到 {len(self.analysis_results['anomalies'])} 项异常（包含连接异常）" if self.analysis_results["anomalies"] else "未检测到异常"
        risk_scan_desc = f"共发现 {len(self.analysis_results['risk_scan'])} 项服务器风险" if self.analysis_results["risk_scan"] else "未配置远程服务器扫描任务"

        # 生成各部分Markdown内容
        config_issues_md = self._format_config_issues()
        vulnerabilities_md = self._format_vulnerabilities()
        anomalies_md = self._format_anomalies()
        risk_scan_md = self._format_risk_scan()

        timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

        # 构建完整的Markdown内容
        markdown_content = f"""# MySQL数据库全面优化报告

**生成时间**: {timestamp}


## 1. 配置问题 ({len(self.analysis_results['config_issues'])})

{config_desc}

{config_issues_md}


## 2. 安全漏洞 ({len(self.analysis_results['vulnerabilities'])})

{vuln_desc}

{vulnerabilities_md}


## 3. 性能与连接异常 ({len(self.analysis_results['anomalies'])})

{anomaly_desc}

{anomalies_md}


## 4. 风险扫描结果 ({len(self.analysis_results['risk_scan'])})

{risk_scan_desc}

{risk_scan_md}


## 5. AI优化建议

{ai_advice}
        """

        # 处理output_file路径
        # 如果output_file是一个目录，则在该目录下生成默认文件名
        if os.path.isdir(output_file):
            output_dir = output_file
            filename = f"mysql_optimization_report_{timestamp.replace(':', '-')}.md"
        else:
            # 使用os.path.join构建文件路径
            output_dir = os.path.dirname(output_file)
            filename = os.path.basename(output_file)

            # 如果filename为空，生成一个默认的文件名
            if not filename:
                filename = f"mysql_optimization_report_{timestamp.replace(':', '-')}.md"

        # 使用os.path.join构建完整路径
        full_path = os.path.join(output_dir, filename)

        # 确保输出目录存在
        os.makedirs(output_dir, exist_ok=True)

        # 先创建文件，再写入内容
        try:
            # 尝试创建文件，如果文件已存在则会抛出FileExistsError
            with open(full_path, "x", encoding="utf-8") as f:
                pass  # 创建空文件
            print(f"\n已创建新文件: {full_path}")
        except FileExistsError:
            print(f"\n文件已存在: {full_path}，将覆盖原有内容")

        # 写入Markdown内容
        with open(full_path, "w", encoding="utf-8") as f:
            f.write(markdown_content.strip())
        print(f"\n报告已生成: {full_path}")

    # Markdown格式化辅助方法
    def _format_config_issues(self):
        if not self.analysis_results["config_issues"]:
            return """未发现具体配置问题，建议参考AI优化建议进行常规配置检查。主要检查方向包括：
- 内存分配与缓冲池设置
- 连接数与线程设置
- 日志配置与性能监控
- 临时表与缓存设置"""

        items = []
        for idx, issue in enumerate(self.analysis_results["config_issues"], 1):
            items.append(f"""{idx}. **{issue['item']}**
   - 当前值: {issue['current_value']}
   - 问题描述: {issue['issue']}
   - 优化建议: {issue['suggestion']}""")

        return "\n\n".join(items)

    def _format_vulnerabilities(self):
        if not self.analysis_results["vulnerabilities"]:
            return """未发现已知安全漏洞，建议执行以下常规安全措施：
- 定期更新MySQL版本以修复潜在安全问题
- 限制数据库用户权限，遵循最小权限原则
- 定期更换数据库访问密码，使用强密码策略
- 限制数据库端口的网络访问范围"""

        items = []
        for idx, vuln in enumerate(self.analysis_results["vulnerabilities"], 1):
            items.append(f"""{idx}. **{vuln['cve_id']}**（{vuln['severity']}风险）
   - 漏洞描述: {vuln['description']}
   - 影响范围: {vuln.get('impact', '未明确说明')}
   - 修复建议: {vuln.get('solution', '升级到安全版本或应用官方补丁')}""")

        return "\n\n".join(items)

    def _format_anomalies(self):
        if not self.analysis_results["anomalies"]:
            return """未检测到异常，系统运行稳定。建议持续监控以下关键指标：
- 数据库连接状态与3306端口可用性
- 连接数与并发查询数
- CPU、内存与磁盘I/O使用率
- 慢查询数量与执行时间"""

        items = []
        for idx, anomaly in enumerate(self.analysis_results["anomalies"], 1):
            # 格式化时间戳
            timestamp = anomaly['timestamp'].strftime('%Y-%m-%d %H:%M:%S') if hasattr(anomaly['timestamp'], 'strftime') else anomaly['timestamp']

            # 在f-string外部处理换行符替换
            analysis_text = anomaly['analysis'].replace('\n', '\n   ')
            temp_fix = anomaly.get('temporary_fix', '建议检查相关指标并优化').replace('\n', '\n   ')

            items.append(f"""{idx}. **异常发生时间: {timestamp}**
   - 异常详情: {analysis_text}
   - 可能原因: {'; '.join(anomaly.get('possible_causes', ['需要进一步分析']))}
   - 临时措施: {temp_fix}""")

        return "\n\n".join(items)

    def _format_risk_scan(self):
        if not self.analysis_results["risk_scan"]:
            return """未配置远程服务器扫描任务。如需进行远程服务器风险评估，请在配置中添加服务器信息。远程扫描主要检查以下内容：
- 服务器3306端口可连接性
- 数据库认证与授权
- 服务器基本安全配置
- 性能指标与潜在瓶颈"""

        items = []
        for idx, server in enumerate(self.analysis_results["risk_scan"], 1):
            issue_list = "\n   - ".join([p["description"] for p in server['anomaly_patterns']]) if server['anomaly_patterns'] else "无具体异常模式"
            items.append(f"""{idx}. **服务器: {server['host']}:{server['port']}**
   - 风险等级: {server['risk_level']}
   - 检测时间: {server['timestamp']}
   - 状态: {server['status']}
   - 异常数量: {server.get('anomaly_count', 0)}
   - 发现问题:
     - {issue_list}""")

        return "\n\n".join(items)

if __name__ == "__main__":
    # 初始化配置
    try:
        init_config()
        print(f"当前AI提供商: {current_provider}")
        print(f"提供商类型: {provider_config['type']}")
        print(f"模型: {AI_MODEL}")
        print(f"API地址: {AI_API_URL}")
        
        # 使用默认的数据库配置（UI/config.json主要包含AI配置）
        DB_CONFIG = {
            "host": "127.0.0.1",
            "user": "root", 
            "password": "root",
            "port": 3306,
            "database": "business_db"
        }
        REMOTE_SERVERS = []  # 从UI配置中获取远程服务器列表
        METRICS_DURATION = 60  # 默认监控时长
        OUTPUT_FILE = REPORT_OUTPUT_FILE
        
        print(f"AI配置已加载: {current_provider} -> {provider_config['type']}")
        
    except Exception as e:
        print(f"配置加载失败: {e}")
        print("使用默认配置")
        DB_CONFIG = {
            "host": "127.0.0.1",
            "user": "root",
            "password": "root", 
            "port": 3306,
            "database": "business_db"
        }
        REMOTE_SERVERS = []
        METRICS_DURATION = 60
        OUTPUT_FILE = REPORT_OUTPUT_FILE

    # 执行分析流程
    print(f"\n=== 开始MySQL分析，使用AI提供商: {current_provider} ===")
    analyzer = MySQLFullAnalyzer(DB_CONFIG)
    analyzer.run_full_analysis(
        scan_servers=REMOTE_SERVERS,
        metrics_duration=METRICS_DURATION
    )

    # 生成最终报告
    analyzer.generate_report(OUTPUT_FILE)
